package henu.dao.impl;
/**
 * 管理人员信息
 * @author 王竞赛
 *
 * 2014-1-20
 *
 */
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import henu.bean.ManagePerson;
import henu.dao.ManagePersonDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

public class ManagePersonDaoImpl implements ManagePersonDao {
	@Override
	public int add(ManagePerson mp) {
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		String sql = "insert into MANAGEPERSON (maid, comid, phone, realName, sex, email, password, questions, answer ,photo, copyIdCard, role, audio, registTime, lastTime, memo) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { mp.getMaid(), mp.getComid(), mp.getPhone(),
				mp.getRealName(), mp.getSex(), mp.getEmail(),
				mp.getPassword(), mp.getQuestions(), mp.getAnswer(),
				mp.getPhoto(), mp.getCopyIdCard(), mp.getRole(), mp.getAudio(),
				mp.getRegistTime(), mp.getLastTime(), mp.getMemo()};
		try {
			result  = runner.update(sql,params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int deleteById(String maid) {
		int result = 0;
		String sql = "delete from MANAGEPERSON where maid=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			result = qr.update(sql, maid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(String maid,ManagePerson mp) {
		int result = 0;
		try {
			QueryRunner qr =DaoFactory.getRunner();
			String sql = "update  MANAGEPERSON  set comid = ?,phone = ?,realName = ?,sex = ?,email = ?,password = ?,questions = ?,answer = ?,photo = ?,copyIdCard = ?,role = ?,audio = ?,registTime = ?,lastTime = ?,memo = ?  where maid = ?";
			System.out.println(sql);
			Object[] params = {mp.getComid(),mp.getPhone(),mp.getRealName(),mp.getSex(),mp.getEmail(),mp.getPassword(),mp.getQuestions(),mp.getAnswer(),mp.getPhone(),mp.getCopyIdCard(),mp.getRole(),mp.getAudio(),mp.getRegistTime(),mp.getLastTime(),mp.getMemo(), maid};
			result = qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	//每次登陆成功更新上一次访问时间
		@Override
		public void updateLastTime(String maid){
			
			try {
				QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
				String sql="update MANAGEPERSON set lastTime=? where maid=?";
				Object[] params={new Date().toLocaleString(),maid};
				qr.update(sql,params);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	@Override
	public ManagePerson findByMaidOrPhone(String maid,String phone){
		ManagePerson m=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from MANAGEPERSON where maid=? or phone=?";
			Object[] params={maid,phone};
			m=qr.query(sql, new BeanHandler<ManagePerson>(ManagePerson.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}
	/* (non-Javadoc)
	 * @see henu.dao.ManagePersonDao#login(java.lang.String, java.lang.String)
	 * 判断登录
	 */
	@Override
	public ManagePerson login(String value,String password){
		ManagePerson mp=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from MANAGEPERSON where (phone=? or maid=?) and password=?";
			Object[] params={value,value,password};
			mp=qr.query(sql, new BeanHandler<ManagePerson>(ManagePerson.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mp;
	}
	
	@Override
	public ManagePerson findById(String maid) {
		ManagePerson mp = new ManagePerson();
		try {
			QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
			String sql = "select * from MANAGEPERSON where maid = ?";
		
			mp = qr.query(sql, new BeanHandler<ManagePerson>(ManagePerson.class), maid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mp;
	}
	@Override
	public List<ManagePerson> findAll() {
		String sql = "select * from MANAGEPERSON";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		List<ManagePerson> list = null;
		try {
			list = qr.query(sql, new BeanListHandler<ManagePerson>(
					ManagePerson.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<ManagePerson> findAllByComId(String property, String key,int comId,
			String order, String sort,int start,int end) {
		List<ManagePerson> list=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select mp.* from manageperson mp where "+property+"='"+key+"' and mp.comId=? order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			list=qr.query(sql, new BeanListHandler<ManagePerson>(ManagePerson.class),comId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public List<ManagePerson> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		List<ManagePerson> list = null;
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from MANAGEPERSON where "
				+ property
				+ "='"
				+ key
				+ "'  order by "
				+ order
				+ " "
				+ sort
				+ ") tt WHERE ROWNUM <=" + end + ") WHERE ro > " + start + "";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list = runner.query(sql, new BeanListHandler<ManagePerson>(
					ManagePerson.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}
